package com.zzzzzz.oms.orderHead;


import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import javax.annotation.Resource;

import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Repository;

import com.google.common.collect.Lists;
import com.zzzzzz.oms.translocation.TransLocationService;
import com.zzzzzz.plugins.jdbctemplate.BaseDao;
import com.zzzzzz.plugins.jdbctemplate.DaoUtils;
import com.zzzzzz.plugins.jdbctemplate.Finder;
import com.zzzzzz.plugins.shiro.I;
import com.zzzzzz.utils.page.Page;

/**
 * @author hing
 * @version 1.0.0
 */
@Repository
public class OrderHeadDao {

	@Resource
	private BaseDao baseDao;
	
	@Resource
	private TransLocationService transLocationService;
	
	// SQL
	private final static String sql_add = "insert into t_order_head(clientId, platformId, cd, shipment_method, vocation, orderTypeId, orderDt, planlDt, planaDt, billingDt, quantity, weight, volume, palletsum, expense, freceiverId, freceiverName, ftranslocationId, freceiverLikename, freceiverPhone, freceiverFax, freceiverEmail, freceiverPostcode, freceiverAddress, treceiverId, treceiverName, ttranslocationId, treceiverLikename, treceiverPhone, treceiverFax, treceiverEmail, treceiverPostcode, treceiverAddress, descr,addDt, addBy, status) values(:clientId, :platformId, :cd, :shipment_method, :vocation, :orderTypeId, :orderDt, :planlDt, :planaDt, :billingDt, :quantity, :weight, :volume, :palletsum, :expense, :freceiverId, :freceiverName, :ftranslocationId, :freceiverLikename, :freceiverPhone, :freceiverFax, :freceiverEmail, :freceiverPostcode, :freceiverAddress, :treceiverId, :treceiverName, :ttranslocationId, :treceiverLikename, :treceiverPhone, :treceiverFax, :treceiverEmail, :treceiverPostcode, :treceiverAddress, :descr, :addDt, :addBy, 'INPUT')";
	private final static String sql_updStByIds = "update t_order_head set status = :status, updDt = :updDt, updBy = :updBy where id in (:ids)";
	private final static String sql_upd = "update t_order_head set clientId=:clientId, platformId=:platformId, cd=:cd, vocation=:vocation,shipment_method=:shipment_method, orderTypeId=:orderTypeId, orderDt=:orderDt, planlDt=:planlDt, planaDt=:planaDt, billingDt=:billingDt, quantity=:quantity, weight=:weight, volume=:volume, palletsum=:palletsum, expense=:expense, freceiverId=:freceiverId, freceiverName=:freceiverName, ftranslocationId=:ftranslocationId, freceiverLikename=:freceiverLikename, freceiverPhone=:freceiverPhone, freceiverFax=:freceiverFax, freceiverEmail=:freceiverEmail, freceiverPostcode=:freceiverPostcode, freceiverAddress=:freceiverAddress, treceiverId=:treceiverId, treceiverName=:treceiverName, ttranslocationId=:ttranslocationId, treceiverLikename=:treceiverLikename, treceiverPhone=:treceiverPhone, treceiverFax=:treceiverFax, treceiverEmail=:treceiverEmail, treceiverPostcode=:treceiverPostcode, treceiverAddress=:treceiverAddress, descr=:descr, updDt=:updDt, updBy=:updBy, extendfield01=:extendfield01, extendfield02=:extendfield02, extendfield03=:extendfield03, extendfield04=:extendfield04, extendfield05=:extendfield05, extendfield06=:extendfield06, extendfield07=:extendfield07, extendfield08=:extendfield08, extendfield09=:extendfield09, extendfield10=:extendfield10, extendfield11=:extendfield11, extendfield12=:extendfield12, extendfield13=:extendfield13, extendfield14=:extendfield14, extendfield15=:extendfield15, extendfield16=:extendfield16, extendfield17=:extendfield17, extendfield18=:extendfield18, extendfield19=:extendfield19, extendfield20=:extendfield20, extendfield21=:extendfield21, extendfield22=:extendfield22, extendfield23=:extendfield23, extendfield24=:extendfield24, extendfield25=:extendfield25, extendfield26=:extendfield26, extendfield27=:extendfield27, extendfield28=:extendfield28, extendfield29=:extendfield29, extendfield30=:extendfield30, status=:status where id = :id";
	private final static String sql_findById = "select o.*,l.name as ftranslocationName,ll.name as ttranslocationName from t_order_head o inner join t_trans_location l on l.id=o.ftranslocationId  inner join t_trans_location ll on ll.id=o.ttranslocationId where o.id =:id";
	private final static String sql_findByIds = "select id, clientId, cd from t_order_head where id in (:ids)";
	private final static String sql_delByIds = "delete from t_order_head where id in(:ids)";
	private final static String sql_updAccountById = "update t_order_head set account = :account,updDt = :updDt, updBy = :updBy where id = :id";
	private final static String sql_updRtimeById = "update t_order_head set rtime = :rtime,updDt = :updDt, updBy = :updBy where id in (:ids)";
	
	// 增加
	public Long add(OrderHead orderHead) {
		return baseDao.updateGetLongKey(sql_add, orderHead);
	}
	
	// 更新状态，主要是用于生效和时效订单
	public int updStByIds(List<Long> ids, String status, I i) {
		Finder finder = new Finder(sql_updStByIds).setParam("ids", ids)
				.setParam("status", status).setParam("updDt", new Date())
				.setParam("updBy", i.getId());
		return baseDao.update(finder);
	}
	
	// 修改数据
	public int updById(OrderHead orderHead) {
		return baseDao.update(sql_upd, orderHead);
	}

	// 配送异常情况登记，主要用于分段订单的跟踪异常登记
	public int updAccountById(Long id,String account,I i) {
		Finder finder = new Finder(sql_updAccountById).setParam("id", id).setParam("account", account)
				.setParam("updDt", new Date()).setParam("updBy", i.getId());
		return baseDao.update(finder);
	}
	
	// 退货时间，主要用于分段订单退货确认
	public int updRtimeById(List<Long> ids,String rtime,I i) {
		Finder finder = new Finder(sql_updRtimeById).setParam("ids", ids).setParam("rtime", rtime)
				.setParam("updDt", new Date()).setParam("updBy", i.getId());
		return baseDao.update(finder);
	}
	
	// 查找一条数据
	public OrderHead findById(Long id) {
		Finder finder = new Finder(sql_findById).setParam("id", id);
		return baseDao.findOne(finder, OrderHead.class);
	}
	
	// 删除订单
	public int delByIds(List<Long> ids) {
		Finder finder = new Finder(sql_delByIds).setParam("ids", ids);
		return baseDao.update(finder);
	}
	/*
	 * 通过id查找对象，主要适用于订单导入查找订单
	 */
	public List<OrderHead> findByIds(List<Long> ids) {  
		Finder finder = new Finder(sql_findByIds).setParam("ids", ids);
        List<OrderHead> list = baseDao.findList(finder, OrderHead.class);
        return list;
	}

	// 通过cd和clientId查找OrderHead，主要使用订单导入验证订单的存在性
	public OrderHead findByClientIdAndCd(String cd,Long clientId){
		Finder finder = new Finder("");
		finder.append("select");
		finder.append("id, clientId, platformId, cd from t_order_head");
		finder.append("where cd = :cd").setParam("cd", cd);
		finder.append("and clientId = :clientId").setParam("clientId", clientId);
		List<OrderHead> orderHead=baseDao.findList(finder, OrderHead.class);
		if(orderHead.size()>0){
			return orderHead.get(0);
		}
		return null;
	}
	
	/**
	 * 动态查询
	 */
	public List<OrderHead> findListBy(Page page, Map<String, Object> ffMap,I i) {
		Finder finder = new Finder("");
		finder.append("select");
		finder.append("count(1) as tt");
		finder.append("from t_order_head o inner join t_client c on o.clientId=c.id inner join t_order_type t on o.orderTypeId=t.id inner join sys_dict d on o.vocation=d.descr left join sys_dict dd on o.shipment_method=dd.descr inner join sys_dict dt on o.status=dt.descr "
				+ " inner join t_trans_location l on l.id=o.ftranslocationId inner join t_trans_location ll on ll.id=o.ttranslocationId inner join t_receiver f on f.id=o.freceiverId inner join t_receiver tt on tt.id=o.treceiverId left join t_user_client u on o.clientId=u.clientId");
		finder.append("where 1=1");
		finder.append("and o.platformId = :platformId").setParam("platformId", i.getPlatformId());
		finder.append("and u.userId = :userId").setParam("userId", i.getId());
		if(DaoUtils.isNotNull(ffMap.get("orderDt1"))){
			finder.append("and date(orderDt) >= date_format(:orderDt1,'%Y-%m-%d')").setParam("orderDt1", ffMap.get("orderDt1"));	
		}
		if(DaoUtils.isNotNull(ffMap.get("orderDt2"))){
			finder.append("and date(orderDt) <= date_format(:orderDt2,'%Y-%m-%d')").setParam("orderDt2", ffMap.get("orderDt2"));
		}
		if(DaoUtils.isNotNull(ffMap.get("planaDt1"))){
			finder.append("and date(planaDt) >= date_format(:planaDt1,'%Y-%m-%d')").setParam("planaDt1", ffMap.get("planaDt1"));
		}
		if(DaoUtils.isNotNull(ffMap.get("planaDt2"))){
			finder.append("and date(planaDt) <= date_format(:planaDt2,'%Y-%m-%d')").setParam("planaDt2", ffMap.get("planaDt2"));
		}
		if (DaoUtils.isNotNull(ffMap.get("clientId"))) {
			finder.append("and o.clientId = :clientId").setParam("clientId",
					ffMap.get("clientId"));
		}
		if (DaoUtils.isNotNull(ffMap.get("cd"))) {
			finder.append("and o.cd = :cd").setParam("cd",
					ffMap.get("cd"));
		}
		if (DaoUtils.isNotNull(ffMap.get("vocation"))) {
			finder.append("and o.vocation = :vocation").setParam("vocation",
					ffMap.get("vocation"));
		}
		if (DaoUtils.isNotNull(ffMap.get("orderId"))) {
			finder.append("and o.id = :id").setParam("id",
					ffMap.get("orderId"));
		}
		if (DaoUtils.isNotNull(ffMap.get("cd1"))) {
			String str = ffMap.get("cd1").toString();
			List<String> list = new ArrayList<String>();
			 StringTokenizer st = new StringTokenizer(str, "\t\n\r\f" + " " + ",");
			 while(st.hasMoreTokens()){
	        	list.add(st.nextToken());
	        }
			finder.append("and o.cd  in(:cd1)").setParam("cd1", list);
		}
		if (DaoUtils.isNotNull(ffMap.get("status"))) {
			finder.append("and o.status = :status").setParam("status", ffMap.get("status"));
		}
		finder.append("order by o.id desc");
		if (page != null) {
			Integer total = baseDao.findInt(finder);
			page.setTotal(total);
			finder.limit(page.getStart(), page.getOffset());
		}

		String sql = finder
				.getSql()
				.replace(
						"count(1) as tt",
						"o.id, o.clientId, o.cd, o.vocation, o.shipment_method, o.orderDt, o.planlDt, o.planaDt, o.billingDt, o.palletsum, o.expense, o.freceiverId, o.freceiverName, o.ftranslocationId, o.freceiverLikename, o.freceiverPhone, o.freceiverFax, o.freceiverEmail, o.freceiverPostcode, freceiverAddress, o.treceiverId, o.treceiverName, o.ttranslocationId, o.treceiverLikename, o.treceiverPhone, o.treceiverFax, o.treceiverEmail, o.treceiverPostcode, o.treceiverAddress, o.descr, o.status,"
						+ "cast(o.quantity as decimal(9,2)) as quantity,cast(o.volume as decimal(12,6)) as volume,cast(o.weight as decimal(12,6)) as weight,c.name as clientName ,t.name as typeName,t.cd as orderTypeCd,d.val as vocationName,dd.val as shipmentMethodName,dt.val as statusName,l.name as ftranslocationName,ll.name as ttranslocationName,f.cd as freceiverCd,tt.cd as treceiverCd");
		finder.setSql(sql);
		List<OrderHead> list = baseDao.findList(finder, OrderHead.class);
		return list;
	}

	/*
	 * 批量出入数据
	 */
	public List<Long> saveOrder(List<OrderHead> orderheads, I i) throws Exception {
		List<Long> list = new ArrayList<Long>();
		for(OrderHead orderHead : orderheads){
			orderHead.setPlatformId(i.getPlatformId());
			orderHead.setAddBy(i.getId());
			orderHead.setAddDt(new Date());
			long id = add(orderHead);
			list.add(id);
		}
		return list;
	}
}
